/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.ImplementInterface;

import DAL.DBManager;
import Server.DTO.Customer_DTO;
import Server.DTO.SavingBook_DTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author 10520_000
 */
public class SavingBookRepository {

    public static int Insert(SavingBook_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String sql = "Insert into SavingBook values('"
                    + info.getBookID() + "','"
                    + info.getCustomerID() + "','"
                    + info.getTypeID() + "','"
                    + info.getOpeningDate() + "','"
                    + info.getDueDate() + "','"
                    + info.getInterestStartingDate() + "',"
                    + info.getAccountBalance() + ",'"
                    + info.getSBStatus() + "')";
            con.createStatement().executeUpdate(sql);
            iRet = 1;
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    //
    public static int Update(SavingBook_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String sql = "UPDATE SavingBook set "
                    + "BookID='" + info.getBookID() + "',"
                    + "CustomerID='" + info.getCustomerID() + "',"
                    + "TypeID='" + info.getTypeID() + "',"
                    + "OpeningDate='" + info.getOpeningDate() + "',"
                    + "DueDate='" + info.getDueDate() + "',"
                    + "InterestStartingDate='" + info.getInterestStartingDate() + "',"
                    + "AccountBalance=" + info.getAccountBalance() + ","
                    + "SBStatus='" + info.getSBStatus() + "' "
                    + "where BookID='" + info.getBookID() + "'";
            con.createStatement().executeUpdate(sql);
            iRet = 1;
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    public static int Delete(SavingBook_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String sql = "DELETE SAVINGBOOK WHERE BookID='" + info.getBookID() + "'";
            con.createStatement().executeUpdate(sql);
            iRet = 1;
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }

    public static void DeleteAll() {
        Connection con = DBManager.getInstance().getConnection();
        try {
            con.setAutoCommit(false);
            String sql = "DELETE FROM SAVINGBOOK";
            con.createStatement().executeUpdate(sql);
            con.commit();
        } catch (SQLException se) {
            try {
                con.rollback();
            } catch (SQLException ise) {
            }
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException fse) {
            }
        }
    }

    public static ArrayList FindAll() {
        ArrayList arr = new ArrayList();

        try {
            String sql = "SELECT * FROM SAVINGBOOK ORDER BY BookID";
            Connection con = DBManager.getInstance().getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                SavingBook_DTO info = new SavingBook_DTO();
                info.setBookID(rs.getString("BookID"));
                info.setCustomerID(rs.getString("CustomerID"));
                info.setTypeID(rs.getString("TypeID"));
                info.setAccountBalance(Float.parseFloat(rs.getString("AccountBalance")));
                info.setDueDate(rs.getDate("DueDate"));
                // info.setInterestRate(rs.getFloat("InterestRate"));
                info.setInterestStartingDate(rs.getDate("InterestStartingDate"));
                info.setOpeningDate(rs.getDate("OpeningDate"));
                info.setSBStatus(rs.getBoolean("SBStatus"));

                arr.add(info);
            }

            stmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }

    public static SavingBook_DTO FindByBookID(String BookID) {
        SavingBook_DTO info = new SavingBook_DTO();

        try {
            String sql = "SELECT * FROM SAVINGBOOK WHERE BookID='" + BookID + "'";
            Connection con = DBManager.getInstance().getConnection();
            ResultSet rs = con.createStatement().executeQuery(sql);

            while (rs.next()) {
                info.setBookID(rs.getString("BookID"));
                info.setCustomerID(rs.getString("CustomerID"));
                info.setTypeID(rs.getString("TypeID"));
                info.setAccountBalance(Float.parseFloat(rs.getString("AccountBalance")));
                info.setDueDate(rs.getDate("DueDate"));
                //info.setInterestRate(rs.getFloat("InterestRate"));
                info.setInterestStartingDate(rs.getDate("InterestStartingDate"));
                info.setOpeningDate(rs.getDate("OpeningDate"));
                info.setSBStatus(rs.getBoolean("SBStatus"));
            }
        } catch (SQLException se) {
            System.out.println(se);
        }
        return info;
    }

    public static ArrayList GetSavingBooks(String str_customerid) throws SQLException {
        ArrayList arr = new ArrayList();
        String sql = "select SAVINGBOOK.BookID, SAVINGBOOK.CustomerID,  SAVINGBOOK.TypeID, OpeningDate,DueDate, InterestStartingDate, AccountBalance, SBStatus "
                + "from (SAVINGBOOK inner join CUSTOMER on CUSTOMER.CustomerID=SAVINGBOOK.CustomerID) inner join SAVINGTYPE on SAVINGTYPE.TypeID=SAVINGBOOK.TypeID "
                + "where FullName like '%"
                + str_customerid + "%'";
        //lay ten khach hang

        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) {
            SavingBook_DTO result = new SavingBook_DTO();
            result.setBookID(rs.getString("BookID"));
            result.setCustomerID(rs.getString("CustomerID"));
            result.setTypeID(rs.getString("TypeID"));
            result.setAccountBalance(Float.parseFloat(rs.getString("AccountBalance")));
            result.setDueDate(rs.getDate("DueDate"));
            //info.setInterestRate(rs.getFloat("InterestRate"));
            result.setInterestStartingDate(rs.getDate("InterestStartingDate"));
            result.setOpeningDate(rs.getDate("OpeningDate"));
            result.setSBStatus(rs.getBoolean("SBStatus"));
            arr.add(result);
        }
        return arr;
    }

    //dem so luong so tiet kiem cua khach hang
    public static int CountSavingbooks(String str_CustomerID) throws SQLException {
        int result = 0;
        String sql = "select COUNT(*) as SoLuongSTK\n"
                + "from CUSTOMER inner join SAVINGBOOK on CUSTOMER.CustomerID=SAVINGBOOK.CustomerID\n"
                + "where SAVINGBOOK.CustomerID='" + str_CustomerID + "'";
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) {
            result = Integer.parseInt(rs.getString("SoLuongSTK"));
        }
        return result;
    }

    public static String GetBookID(String str_FullName, String str_IDNumber, String str_TypeName, int term) throws SQLException {
        String result = "";
        String sql = "select distinct SAVINGBOOK.BookID "
                + "from (SAVINGBOOK inner join CUSTOMER on CUSTOMER.CustomerID=SAVINGBOOK.CustomerID) inner join SAVINGTYPE on SAVINGTYPE.TypeID=SAVINGBOOK.TypeID "
                + "where FullName='" + str_FullName + "' and IDNumber='" + str_IDNumber + "' and TypeName='" + str_TypeName + "' AND Term=" + term;
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) {
            result = rs.getString("BookID");
        }
        return result;
    }

    public static void main(String[] str) throws SQLException {
        SavingBook_DTO info = new SavingBook_DTO();
        System.out.println(GetBookID("do ngoc sam", "215250401", "CO KY HAN", 1));
    }
}
